//
//  BYJDataBaseHandle.m
//  UI14_豆瓣完整版
//
//  Created by dllo on 16/1/8.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "BYJDataBaseHandle.h"
#import "MovieModel.h"
#import "ActivityModel.h"
#import "UserModel.h"

@implementation BYJDataBaseHandle

+ (BYJDataBaseHandle *)sharedDataBaseHandle {
    
    static BYJDataBaseHandle *handle = nil;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[BYJDataBaseHandle alloc] init];
    });
    return handle;
}

// 打开沙盒
- (void)openDB {
    
    NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    NSString *dbPath = [document stringByAppendingPathComponent:@"BaiYunJie.sqlite"];
    
    NSLog(@"沙盒 : %@", dbPath);
    
    int result = sqlite3_open(dbPath.UTF8String, &dbPoint);
    
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }
    
}

// 打开movie表
- (void)createMoviewTabel {
    
    // sql 语句
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists Movie(number integer primary key autoincrement, title text)"];
   
    // 让数据库执行sql语句
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"Movie 建表成功");
    } else {
        NSLog(@"Movie 建表失败");
    }
}

// 打开activity表
- (void)createActivityTabel {
    
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists Activity(number integer primary key autoincrement, title text)"];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"Activity 建表成功");
    } else {
        NSLog(@"Activity 建表失败");
    }
    
}

// 创建user表
- (void)createUserTabel {
    
    // sql 语句
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists User(number integer primary key autoincrement, name text, code text, code1 text, email text, phone text)"];
    
    // 让数据库执行sql语句
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"user 建表成功");
    } else {
        NSLog(@"user 建表失败");
    }
}

// 插入到movie
- (void)insertMovieModel:(MovieModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Movie(title) values('%@')", model.title];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"movie 插入成功");
    } else {
        NSLog(@"movie 插入失败");
    }
    
}

// 插入到activity
- (void)insertActivityModel:(ActivityModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Activity(title) values('%@')", model.title];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"activity 插入成功");
    } else {
        NSLog(@"activity 插入失败");
    }
    
}

// 插入到user
- (void)insertUserModel:(UserModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into User(name, code, code1, email, phone) values('%@', '%@', '%@', '%@', '%@')", model.name, model.code, model.code1, model.email, model.phone];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"user插入成功");
    } else {
        NSLog(@"user插入失败");
    }
    
}

// 删除movie
- (void)deleteMovieModel:(MovieModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Movie"];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"movie 删除成功");
    } else {
        NSLog(@"movie 删除失败");
    }
    
}

// 删除activity
- (void)deleteActivityModel:(ActivityModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Activity"];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"activity 删除成功");
    } else {
        NSLog(@"activity 删除失败");
    }
}

// 删除user
- (void)deleteUserModel:(UserModel *)user {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from User"];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

// 查询movie
- (NSMutableArray *)selectMovieTable {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from Movie"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    NSMutableArray *arrModel = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            
            MovieModel *model = [MovieModel movieModel];
            model.title = [NSString stringWithUTF8String:(const char*) title];
            [arrModel addObject:model];
        }
    }
    return arrModel;
}

// 查询activity
- (NSMutableArray *)selectActivityTable {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from Activity"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    NSMutableArray *arrModel = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            ActivityModel *model = [ActivityModel activityModel];
            model.title = [NSString stringWithUTF8String:(const char*) title];
            [arrModel addObject:model];
        }
    }
    return arrModel;
}

// 查询user
- (NSMutableArray *)selectUserTable {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from User"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    NSMutableArray *arrModel = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *code = sqlite3_column_text(stmt, 2);
            const unsigned char *code1 = sqlite3_column_text(stmt, 3);
            const unsigned char *email = sqlite3_column_text(stmt, 4);
            const unsigned char *phone = sqlite3_column_text(stmt, 5);
         
            UserModel *model = [UserModel userModel];
            model.name = [NSString stringWithUTF8String:(const char*) name];
            model.code = [NSString stringWithUTF8String:(const char*) code];
            model.code1 = [NSString stringWithUTF8String:(const char*) code1];
            model.email = [NSString stringWithUTF8String:(const char*) email];
            model.phone = [NSString stringWithUTF8String:(const char*) phone];
            
            [arrModel addObject:model];
        }
    }
    
    return arrModel;
}























@end
